{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# pandas数据加载"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Text<br>\n",
    "CSV<br>\n",
    "Excel<br>\n",
    "HTML<br>\n",
    "MySQL<br>\n",
    "MongoDB<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>jack@csdn.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>lily@qq.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>marry@163.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           email\n",
       "0  jack@csdn.com\n",
       "1    lily@qq.com\n",
       "2  marry@163.com"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_table('data/01.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Company</th>\n",
       "      <th>Mail</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jack</td>\n",
       "      <td>CSDN</td>\n",
       "      <td>jack@csdn.net</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Lily</td>\n",
       "      <td>tencent</td>\n",
       "      <td>lily@qq.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Marry</td>\n",
       "      <td>wangyi</td>\n",
       "      <td>marry@163.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Name  Company           Mail\n",
       "0   Jack     CSDN  jack@csdn.net\n",
       "1   Lily  tencent    lily@qq.com\n",
       "2  Marry   wangyi  marry@163.com"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_table('data/02.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>jack</th>\n",
       "      <th>jack@csdn.com</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>lily</td>\n",
       "      <td>lily@qq.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>marry</td>\n",
       "      <td>marry@163.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    jack  jack@csdn.com\n",
       "0   lily    lily@qq.com\n",
       "1  marry  marry@163.com"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_table('data/03.txt', sep=':')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>jack</td>\n",
       "      <td>jack@csdn.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>lily</td>\n",
       "      <td>lily@qq.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>marry</td>\n",
       "      <td>marry@163.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       0              1\n",
       "0   jack  jack@csdn.com\n",
       "1   lily    lily@qq.com\n",
       "2  marry  marry@163.com"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_table('data/03.txt', sep=':', header=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>jack</td>\n",
       "      <td>jack@csdn.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>lily</td>\n",
       "      <td>lily@qq.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>marry</td>\n",
       "      <td>marry@163.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name          email\n",
       "0   jack  jack@csdn.com\n",
       "1   lily    lily@qq.com\n",
       "2  marry  marry@163.com"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_table('data/03.txt', sep=':', names=['name','email'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Phone</th>\n",
       "      <th>Addr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>jack</td>\n",
       "      <td>15964522054</td>\n",
       "      <td>Beijing, China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>lily</td>\n",
       "      <td>17934243244</td>\n",
       "      <td>Shanghai, China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>marry</td>\n",
       "      <td>15354354567</td>\n",
       "      <td>Shenzhen, China</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Name        Phone             Addr\n",
       "0   jack  15964522054   Beijing, China\n",
       "1   lily  17934243244  Shanghai, China\n",
       "2  marry  15354354567  Shenzhen, China"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data/04.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Phone</th>\n",
       "      <th>Addr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>jack</td>\n",
       "      <td>15964522054</td>\n",
       "      <td>Beijing, China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>lily</td>\n",
       "      <td>17934243244</td>\n",
       "      <td>Shanghai, China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>marry</td>\n",
       "      <td>15354354567</td>\n",
       "      <td>Shenzhen, China</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Name        Phone             Addr\n",
       "0   jack  15964522054   Beijing, China\n",
       "1   lily  17934243244  Shanghai, China\n",
       "2  marry  15354354567  Shenzhen, China"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_excel('data/05.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在使用上面这条语句的时候，需要先安装 lxrd 模块:  pip3 install xlrd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## HTML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = pd.read_html('data/06.html', header=0)    #  其中的 header=0指用第一行做为head头"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>名称</th>\n",
       "      <th>IP</th>\n",
       "      <th>CPU</th>\n",
       "      <th>内存</th>\n",
       "      <th>硬盘</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>tencent</td>\n",
       "      <td>202.54.21.124</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>nfp</td>\n",
       "      <td>204.44.68.49</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>DigitalOcean</td>\n",
       "      <td>159.65.130.172</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID            名称              IP  CPU  内存  硬盘\n",
       "0   1       tencent   202.54.21.124    2   1  50\n",
       "1   2           nfp    204.44.68.49    1   1  20\n",
       "2   3  DigitalOcean  159.65.130.172    2   2  25"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tables[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(tables)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用 read_html() 前需要安装 lxml 模块：  pip3 install lxml"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## MySQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用之前需要安装 pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = pymysql.connect(host='127.0.0.1', user='root', password='xxxxxx', db='auto', charset='utf8')  #这里charset不要写utf-8，而是utf8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = 'select * from monitor'\n",
    "data = pd.read_sql(sql, conn)\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>host_id</th>\n",
       "      <th>cpu_used</th>\n",
       "      <th>vir_mem_used</th>\n",
       "      <th>mem_used</th>\n",
       "      <th>addtime</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>10.2</td>\n",
       "      <td>29.7</td>\n",
       "      <td>29.7</td>\n",
       "      <td>2018-08-05 02:31:00</td>\n",
       "      <td>10:31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>10.0</td>\n",
       "      <td>29.8</td>\n",
       "      <td>29.8</td>\n",
       "      <td>2018-08-05 02:31:40</td>\n",
       "      <td>10:31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>29.7</td>\n",
       "      <td>29.7</td>\n",
       "      <td>2018-08-05 02:34:23</td>\n",
       "      <td>10:34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>3.9</td>\n",
       "      <td>30.3</td>\n",
       "      <td>30.3</td>\n",
       "      <td>2018-08-05 02:37:06</td>\n",
       "      <td>10:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>28.6</td>\n",
       "      <td>28.6</td>\n",
       "      <td>2018-08-05 02:43:47</td>\n",
       "      <td>10:43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>8.0</td>\n",
       "      <td>27.2</td>\n",
       "      <td>27.2</td>\n",
       "      <td>2018-08-05 02:44:34</td>\n",
       "      <td>10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>9.8</td>\n",
       "      <td>27.3</td>\n",
       "      <td>27.3</td>\n",
       "      <td>2018-08-05 02:48:46</td>\n",
       "      <td>10:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>28.2</td>\n",
       "      <td>28.2</td>\n",
       "      <td>2018-08-05 02:50:24</td>\n",
       "      <td>10:50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>14</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>27.7</td>\n",
       "      <td>27.7</td>\n",
       "      <td>2018-08-05 02:51:20</td>\n",
       "      <td>10:51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>3.9</td>\n",
       "      <td>27.7</td>\n",
       "      <td>27.7</td>\n",
       "      <td>2018-08-05 02:52:01</td>\n",
       "      <td>10:52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "      <td>8.0</td>\n",
       "      <td>28.3</td>\n",
       "      <td>28.3</td>\n",
       "      <td>2018-08-05 02:52:13</td>\n",
       "      <td>10:52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>17</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>27.6</td>\n",
       "      <td>27.6</td>\n",
       "      <td>2018-08-05 02:53:43</td>\n",
       "      <td>10:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>18</td>\n",
       "      <td>3</td>\n",
       "      <td>14.3</td>\n",
       "      <td>27.8</td>\n",
       "      <td>27.8</td>\n",
       "      <td>2018-08-05 02:54:51</td>\n",
       "      <td>10:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>19</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>28.2</td>\n",
       "      <td>28.2</td>\n",
       "      <td>2018-08-05 02:56:16</td>\n",
       "      <td>10:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>12.0</td>\n",
       "      <td>28.9</td>\n",
       "      <td>28.9</td>\n",
       "      <td>2018-08-05 02:56:31</td>\n",
       "      <td>10:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>21</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>29.9</td>\n",
       "      <td>29.9</td>\n",
       "      <td>2018-08-05 03:01:46</td>\n",
       "      <td>11:1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>22</td>\n",
       "      <td>3</td>\n",
       "      <td>11.8</td>\n",
       "      <td>29.8</td>\n",
       "      <td>29.8</td>\n",
       "      <td>2018-08-05 03:02:18</td>\n",
       "      <td>11:2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>23</td>\n",
       "      <td>3</td>\n",
       "      <td>8.0</td>\n",
       "      <td>30.3</td>\n",
       "      <td>30.3</td>\n",
       "      <td>2018-08-05 03:03:40</td>\n",
       "      <td>11:3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>24</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>29.9</td>\n",
       "      <td>29.9</td>\n",
       "      <td>2018-08-05 03:03:42</td>\n",
       "      <td>11:3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>25</td>\n",
       "      <td>3</td>\n",
       "      <td>11.8</td>\n",
       "      <td>30.5</td>\n",
       "      <td>30.5</td>\n",
       "      <td>2018-08-05 03:07:10</td>\n",
       "      <td>11:7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>26</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>30.6</td>\n",
       "      <td>30.6</td>\n",
       "      <td>2018-08-05 04:00:13</td>\n",
       "      <td>12:0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id  host_id cpu_used vir_mem_used mem_used             addtime   time\n",
       "0    6        3     10.2         29.7     29.7 2018-08-05 02:31:00  10:31\n",
       "1    7        3     10.0         29.8     29.8 2018-08-05 02:31:40  10:31\n",
       "2    8        3      2.0         29.7     29.7 2018-08-05 02:34:23  10:34\n",
       "3    9        3      3.9         30.3     30.3 2018-08-05 02:37:06  10:37\n",
       "4   10        3      2.0         28.6     28.6 2018-08-05 02:43:47  10:43\n",
       "5   11        3      8.0         27.2     27.2 2018-08-05 02:44:34  10:44\n",
       "6   12        3      9.8         27.3     27.3 2018-08-05 02:48:46  10:48\n",
       "7   13        3      4.0         28.2     28.2 2018-08-05 02:50:24  10:50\n",
       "8   14        3      2.0         27.7     27.7 2018-08-05 02:51:20  10:51\n",
       "9   15        3      3.9         27.7     27.7 2018-08-05 02:52:01  10:52\n",
       "10  16        3      8.0         28.3     28.3 2018-08-05 02:52:13  10:52\n",
       "11  17        3      2.0         27.6     27.6 2018-08-05 02:53:43  10:53\n",
       "12  18        3     14.3         27.8     27.8 2018-08-05 02:54:51  10:54\n",
       "13  19        3      2.0         28.2     28.2 2018-08-05 02:56:16  10:56\n",
       "14  20        3     12.0         28.9     28.9 2018-08-05 02:56:31  10:56\n",
       "15  21        3      2.0         29.9     29.9 2018-08-05 03:01:46   11:1\n",
       "16  22        3     11.8         29.8     29.8 2018-08-05 03:02:18   11:2\n",
       "17  23        3      8.0         30.3     30.3 2018-08-05 03:03:40   11:3\n",
       "18  24        3      2.0         29.9     29.9 2018-08-05 03:03:42   11:3\n",
       "19  25        3     11.8         30.5     30.5 2018-08-05 03:07:10   11:7\n",
       "20  26        3      2.0         30.6     30.6 2018-08-05 04:00:13   12:0"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## MongoDB"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用之前需要安装 pymongo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymongo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "client = pymongo.MongoClient('127.0.0.1', 27017)   # mongodb的默认端口号为 27017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MongoClient(host=['127.0.0.1:27017'], document_class=dict, tz_aware=False, connect=True)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "client"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "db = 'local'\n",
    "table = 'startup_log'\n",
    "cursor = client[db][table].find()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.DataFrame(list(cursor))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>buildinfo</th>\n",
       "      <th>cmdLine</th>\n",
       "      <th>hostname</th>\n",
       "      <th>pid</th>\n",
       "      <th>startTime</th>\n",
       "      <th>startTimeLocal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ziyichen-1528717249925</td>\n",
       "      <td>{'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...</td>\n",
       "      <td>{'config': '/usr/local/etc/mongod.conf', 'net'...</td>\n",
       "      <td>ziyichen</td>\n",
       "      <td>65156</td>\n",
       "      <td>2018-06-11 11:40:49</td>\n",
       "      <td>Mon Jun 11 19:40:49.925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ziyichen.local-1528802746726</td>\n",
       "      <td>{'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...</td>\n",
       "      <td>{'config': '/usr/local/etc/mongod.conf', 'net'...</td>\n",
       "      <td>ziyichen.local</td>\n",
       "      <td>482</td>\n",
       "      <td>2018-06-12 11:25:46</td>\n",
       "      <td>Tue Jun 12 19:25:46.726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ziyichen.local-1528813813064</td>\n",
       "      <td>{'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...</td>\n",
       "      <td>{'config': '/usr/local/etc/mongod.conf', 'net'...</td>\n",
       "      <td>ziyichen.local</td>\n",
       "      <td>788</td>\n",
       "      <td>2018-06-12 14:30:13</td>\n",
       "      <td>Tue Jun 12 22:30:13.064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ziyichen.local-1529591554637</td>\n",
       "      <td>{'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...</td>\n",
       "      <td>{'config': '/usr/local/etc/mongod.conf', 'net'...</td>\n",
       "      <td>ziyichen.local</td>\n",
       "      <td>454</td>\n",
       "      <td>2018-06-21 14:32:34</td>\n",
       "      <td>Thu Jun 21 22:32:34.637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ziyichen.local-1529596200879</td>\n",
       "      <td>{'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...</td>\n",
       "      <td>{'config': '/usr/local/etc/mongod.conf', 'net'...</td>\n",
       "      <td>ziyichen.local</td>\n",
       "      <td>511</td>\n",
       "      <td>2018-06-21 15:50:00</td>\n",
       "      <td>Thu Jun 21 23:50:00.879</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ziyichendeMac.local-1529933646586</td>\n",
       "      <td>{'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...</td>\n",
       "      <td>{'config': '/usr/local/etc/mongod.conf', 'net'...</td>\n",
       "      <td>ziyichendeMac.local</td>\n",
       "      <td>458</td>\n",
       "      <td>2018-06-25 13:34:06</td>\n",
       "      <td>Mon Jun 25 21:34:06.586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ziyichendeMac.local-1530701229819</td>\n",
       "      <td>{'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...</td>\n",
       "      <td>{'config': '/usr/local/etc/mongod.conf', 'net'...</td>\n",
       "      <td>ziyichendeMac.local</td>\n",
       "      <td>486</td>\n",
       "      <td>2018-07-04 10:47:09</td>\n",
       "      <td>Wed Jul  4 18:47:09.819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ziyichendeMac.local-1530806618388</td>\n",
       "      <td>{'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...</td>\n",
       "      <td>{'config': '/usr/local/etc/mongod.conf', 'net'...</td>\n",
       "      <td>ziyichendeMac.local</td>\n",
       "      <td>497</td>\n",
       "      <td>2018-07-05 16:03:38</td>\n",
       "      <td>Fri Jul  6 00:03:38.388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>ziyichen.local-1530850909033</td>\n",
       "      <td>{'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...</td>\n",
       "      <td>{'config': '/usr/local/etc/mongod.conf', 'net'...</td>\n",
       "      <td>ziyichen.local</td>\n",
       "      <td>442</td>\n",
       "      <td>2018-07-06 04:21:49</td>\n",
       "      <td>Fri Jul  6 12:21:49.033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ziyichen.local-1530887751415</td>\n",
       "      <td>{'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...</td>\n",
       "      <td>{'config': '/usr/local/etc/mongod.conf', 'net'...</td>\n",
       "      <td>ziyichen.local</td>\n",
       "      <td>506</td>\n",
       "      <td>2018-07-06 14:35:51</td>\n",
       "      <td>Fri Jul  6 22:35:51.415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>ziyichen.local-1531239840724</td>\n",
       "      <td>{'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...</td>\n",
       "      <td>{'config': '/usr/local/etc/mongod.conf', 'net'...</td>\n",
       "      <td>ziyichen.local</td>\n",
       "      <td>461</td>\n",
       "      <td>2018-07-10 16:24:00</td>\n",
       "      <td>Wed Jul 11 00:24:00.724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ziyichen.local-1532019012709</td>\n",
       "      <td>{'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...</td>\n",
       "      <td>{'config': '/usr/local/etc/mongod.conf', 'net'...</td>\n",
       "      <td>ziyichen.local</td>\n",
       "      <td>542</td>\n",
       "      <td>2018-07-19 16:50:12</td>\n",
       "      <td>Fri Jul 20 00:50:12.709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>ziyichen.local-1532085990421</td>\n",
       "      <td>{'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...</td>\n",
       "      <td>{'config': '/usr/local/etc/mongod.conf', 'net'...</td>\n",
       "      <td>ziyichen.local</td>\n",
       "      <td>790</td>\n",
       "      <td>2018-07-20 11:26:30</td>\n",
       "      <td>Fri Jul 20 19:26:30.421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>ziyichen-1532847648645</td>\n",
       "      <td>{'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...</td>\n",
       "      <td>{'config': '/usr/local/etc/mongod.conf', 'net'...</td>\n",
       "      <td>ziyichen</td>\n",
       "      <td>443</td>\n",
       "      <td>2018-07-29 07:00:48</td>\n",
       "      <td>Sun Jul 29 15:00:48.645</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  _id  \\\n",
       "0              ziyichen-1528717249925   \n",
       "1        ziyichen.local-1528802746726   \n",
       "2        ziyichen.local-1528813813064   \n",
       "3        ziyichen.local-1529591554637   \n",
       "4        ziyichen.local-1529596200879   \n",
       "5   ziyichendeMac.local-1529933646586   \n",
       "6   ziyichendeMac.local-1530701229819   \n",
       "7   ziyichendeMac.local-1530806618388   \n",
       "8        ziyichen.local-1530850909033   \n",
       "9        ziyichen.local-1530887751415   \n",
       "10       ziyichen.local-1531239840724   \n",
       "11       ziyichen.local-1532019012709   \n",
       "12       ziyichen.local-1532085990421   \n",
       "13             ziyichen-1532847648645   \n",
       "\n",
       "                                            buildinfo  \\\n",
       "0   {'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...   \n",
       "1   {'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...   \n",
       "2   {'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...   \n",
       "3   {'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...   \n",
       "4   {'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...   \n",
       "5   {'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...   \n",
       "6   {'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...   \n",
       "7   {'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...   \n",
       "8   {'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...   \n",
       "9   {'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...   \n",
       "10  {'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...   \n",
       "11  {'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...   \n",
       "12  {'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...   \n",
       "13  {'version': '3.6.5', 'gitVersion': 'a20ecd3e3a...   \n",
       "\n",
       "                                              cmdLine             hostname  \\\n",
       "0   {'config': '/usr/local/etc/mongod.conf', 'net'...             ziyichen   \n",
       "1   {'config': '/usr/local/etc/mongod.conf', 'net'...       ziyichen.local   \n",
       "2   {'config': '/usr/local/etc/mongod.conf', 'net'...       ziyichen.local   \n",
       "3   {'config': '/usr/local/etc/mongod.conf', 'net'...       ziyichen.local   \n",
       "4   {'config': '/usr/local/etc/mongod.conf', 'net'...       ziyichen.local   \n",
       "5   {'config': '/usr/local/etc/mongod.conf', 'net'...  ziyichendeMac.local   \n",
       "6   {'config': '/usr/local/etc/mongod.conf', 'net'...  ziyichendeMac.local   \n",
       "7   {'config': '/usr/local/etc/mongod.conf', 'net'...  ziyichendeMac.local   \n",
       "8   {'config': '/usr/local/etc/mongod.conf', 'net'...       ziyichen.local   \n",
       "9   {'config': '/usr/local/etc/mongod.conf', 'net'...       ziyichen.local   \n",
       "10  {'config': '/usr/local/etc/mongod.conf', 'net'...       ziyichen.local   \n",
       "11  {'config': '/usr/local/etc/mongod.conf', 'net'...       ziyichen.local   \n",
       "12  {'config': '/usr/local/etc/mongod.conf', 'net'...       ziyichen.local   \n",
       "13  {'config': '/usr/local/etc/mongod.conf', 'net'...             ziyichen   \n",
       "\n",
       "      pid           startTime           startTimeLocal  \n",
       "0   65156 2018-06-11 11:40:49  Mon Jun 11 19:40:49.925  \n",
       "1     482 2018-06-12 11:25:46  Tue Jun 12 19:25:46.726  \n",
       "2     788 2018-06-12 14:30:13  Tue Jun 12 22:30:13.064  \n",
       "3     454 2018-06-21 14:32:34  Thu Jun 21 22:32:34.637  \n",
       "4     511 2018-06-21 15:50:00  Thu Jun 21 23:50:00.879  \n",
       "5     458 2018-06-25 13:34:06  Mon Jun 25 21:34:06.586  \n",
       "6     486 2018-07-04 10:47:09  Wed Jul  4 18:47:09.819  \n",
       "7     497 2018-07-05 16:03:38  Fri Jul  6 00:03:38.388  \n",
       "8     442 2018-07-06 04:21:49  Fri Jul  6 12:21:49.033  \n",
       "9     506 2018-07-06 14:35:51  Fri Jul  6 22:35:51.415  \n",
       "10    461 2018-07-10 16:24:00  Wed Jul 11 00:24:00.724  \n",
       "11    542 2018-07-19 16:50:12  Fri Jul 20 00:50:12.709  \n",
       "12    790 2018-07-20 11:26:30  Fri Jul 20 19:26:30.421  \n",
       "13    443 2018-07-29 07:00:48  Sun Jul 29 15:00:48.645  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
